/**

This file is part of MaCI/GIMnet.

MaCI/GIMnet is free software: you can redistribute it and/or modify it 
under the terms of the GNU Lesser General Public License as published 
by the Free Software Foundation, either version 3 of the License, or 
(at your option) any later version.

MaCI/GIMnet is distributed in the hope that it will be useful, but WITHOUT 
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or 
FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public 
License for more details.

You should have received a copy of the GNU Lesser General Public 
License along with GIMnet. (See COPYING.LESSER) If not, see 
<http://www.gnu.org/licenses/>.

**/
#MySQL database interfacing class
import MySQLdb, traceback, re, _mysql, time

class DataBase:
	"Common interface for mySQL database, in robot control algorithm"
	def __init__(self):
		"connect and let user know if we have connection by 'running' variable"
		self.running = False

		self.globaloccugridTable = "global_occu_grid"
		self.metaInfoTable = "meta_info"


		try:
			self.connection = MySQLdb.connect(host="127.0.0.1", user="tpirttio", passwd="pCQLeWLpD3aYSCmG", db="tpirttio" )
			self.cursor = MySQLdb.cursors.DictCursor(self.connection) 
		except:
			traceback.print_exc()
			return
		self.running = True
		return



# new code 29.9.2011
	def getGlobalOccuGridCells(self, metaID):
		"""
		get stored grid cells from global occu grid table
		"""
		if not self.running: return	
		
		feats = "id, x, y, occu"
		
		phrase = "select %s from %s WHERE `metaID` = %s " % (feats, self.globaloccugridTable, metaID)
		self.cursor.execute(phrase)
		try:
			ret = self.cursor.fetchall()
		except IndexError:
			ret = {}
		return ret
# new code 30.9.2011
	def getMetaID(self, site_name):
		"""
		find grid meta info with name
		"""
		
		metaID = 0

		feature = "`id`, `site_name`, `x_samples`, `y_samples`, `grid_size`, `sensor_height`, `origo_x`, `origo_y`"
		condition = "site_name = '" + site_name + "' AND sensor_height = '" + str('0.5') + "' "

		phrase = 'select %s FROM %s WHERE %s ORDER BY `id` desc ' % (feature, self.metaInfoTable, condition)
#		print phrase 

		self.cursor.execute(phrase)
		try:
			results = self.cursor.fetchall()

		except IndexError:
			return metaID

		metaID = results[0]['id']

		return metaID

# new code 29.9.2011
	def getMetaIDbyHeight(self, site_name, sensor_height):
		"""
		find grid meta info with name
		"""
		
		metaID = 0

		feature = "`id`, `site_name`, `x_samples`, `y_samples`, `grid_size`, `sensor_height`, `origo_x`, `origo_y`"
		condition = "site_name = '" + site_name + "' AND sensor_height = '" + sensor_height + "' "

		phrase = 'select %s FROM %s WHERE %s ORDER BY `id` desc ' % (feature, self.metaInfoTable, condition)
#		print phrase 

		self.cursor.execute(phrase)
		try:
			results = self.cursor.fetchall()

		except IndexError:
			return metaID

		metaID = results[0]['id']

		return metaID

# new code 29.9.2011
	def getMetaInfo(self, metaID):
		"""
		find grid meta info with name
		gridInfo should be of form
		gridInfo[0] - x_samples
		gridInfo[1] - y_samples
		gridInfo[2] - grid_size
		gridInfo[3] - sensor_height
		gridInfo[4] - origo_x
		gridInfo[5] - origo_y
		"""
		

		feature = "`id`, `site_name`, `x_samples`, `y_samples`, `grid_size`, `sensor_height`, `origo_x`, `origo_y`"
		condition = "`id` = '" + str(metaID) + "'"

		phrase = 'select %s FROM %s WHERE %s ORDER BY `id` desc ' % (feature, self.metaInfoTable, condition)
#		print phrase 

		self.cursor.execute(phrase)
		try:
			results = self.cursor.fetchall()

		except IndexError:
			return {}

		gridInfo = [int(0), int(0), str(''), str(''), str(''), str('')]

		gridInfo[0] = int(results[0]['x_samples'])
		gridInfo[1] = int(results[0]['y_samples'])
		gridInfo[2] = str(results[0]['grid_size'])
		gridInfo[3] = str(results[0]['sensor_height'])
		gridInfo[4] = str(results[0]['origo_x'])
		gridInfo[5] = str(results[0]['origo_y'])
		
		return gridInfo

		
# new code 29.9.2011
	def insertMetaInfo(self, name, gridInfo):
		"""
		insert grid metaInfo to metainfotable

		name should contain name of the site

		gridInfo should be of form
		gridInfo[0] - x_samples
		gridInfo[1] - y_samples
		gridInfo[2] - grid_size
		gridInfo[3] - sensor_height
		gridInfo[4] - origo_x
		gridInfo[5] - origo_y
		"""
		feature = "`site_name`, `x_samples`, `y_samples`, `grid_size`, `sensor_height`, `origo_x`, `origo_y`, `modified`"
		value = "'" + str(name) + "', '" + str(gridInfo[0]) + "', '" + str(gridInfo[1]) + "', '" + str(gridInfo[2]) + "', '" + str(gridInfo[3]) + "', '" + str(gridInfo[4]) + "', '" + str(gridInfo[5]) + "', CURRENT_TIMESTAMP"

		if not self.running: return
		phrase = 'insert into %s(%s) values(%s)' % (self.metaInfoTable, feature, value)	

		print phrase

		self.cursor.execute(phrase)
#		return
		return self.connection.insert_id()

		

# new code 23.9.2011 
	def insertglobalOccuGrid(self, grid, authorId, metaID):
		""" 
		inserts one grid with values to global occu table	

		grid should be of form 
		grid[0] - grid cell x coordinate
		grid[1] - grid cell y coordinate
		grid[2] - grid cell occu values
		
		"""
		feature = "`x`, `y`, `occu`, `modified`, `authorID`, `metaID`"
		value = "'" + str(grid[0]) + "', '" + str(grid[1]) + "', '" + str(grid[2]) + "', CURRENT_TIMESTAMP, '" + str(authorId) + "', '" + str(metaID) + "'"

		if not self.running: return
		phrase = 'insert into %s(%s) values(%s)' % (self.globaloccugridTable, feature, value)	

#		print phrase

		self.cursor.execute(phrase)
		return self.connection.insert_id()


	def close(self):
		"""
		close()
		Close connection to mySQL server."""
		self.cursor.close()    
		self.connection.close() 
		
def ping():
	db = DataBase()
	if db.running:
		db.close()
		return True
	else:
		db.close()
		return False
		

